USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[RekidaKirokuList_All]    スクリプト日付: 05/11/2018 16:18:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RekidaKirokuList_All]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RekidaKirokuList_All]
GO

CREATE PROCEDURE [dbo].[RekidaKirokuList_All]
    @TAIKAINO int
AS
SELECT R.KYOGINO,
       R.SABUNO,
       R.REKIDAINO,
       KY.KYOGIMEI,
       C.COMMENTRYAKU, 
       dbo.Kiroku(R.KIROKU,KY.SYUMOKUKUBUN,KY.KIROKUKUBUN,R.SOKUTEI) AS KIROKU,
       R.SENSYUMEI, 
       R.HIDUKE,
       KY.SYUMOKUKUBUN,
       C.PRINTNO
  FROM TBL_REKIDAI AS R,
       TBL_COMMENT AS C,
        (
            SELECT KYO.KYOGIMEI AS KYOGIMEI
            ,KYO.TAIKAINO AS TAIKAINO
            ,KYO.KYOGINO AS KYOGINO
            ,KM.SYUMOKUKUBUN
            ,KM.KIROKUKUBUN
            FROM
                TBL_KYOGI AS kYO
                    LEFT OUTER JOIN TBL_SYUMOKU_MASTER AS KM 
                    ON KYO.SYUMOKUNO = KM.SYUMOKUNO
        )AS KY
  WHERE R.TAIKAINO = @TAIKAINO
    AND R.COMMENTNO = C.COMMENTNO
    AND R.TAIKAINO = kY.TAIKAINO
    AND R.KYOGINO = kY.KYOGINO
  ORDER BY R.KYOGINO, R.SABUNO, C.PRINTNO
